﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class PhieuThuePhongDAO
    {
        public class SqlConnectionData
        {
            public static SqlConnection Connection()
            {
                SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=QLKSAN;Integrated Security=True");
                return conn;
            }
        }

        public static DataTable GetAll()
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_PhieuThuePhongGetAll", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;

        }
        public static void InsertPhieuThuePhong(PhieuThuePhongDTO pt)
        {
            SqlConnection conn = SqlConnectionData.Connection();
            SqlCommand cmd = new SqlCommand("sp_InsertPhieuThuePhong", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@MaPhong", SqlDbType.Int);
            cmd.Parameters.Add("@NgayThuePhong", SqlDbType.DateTime);
            cmd.Parameters.Add("@NgayKetThuc", SqlDbType.DateTime);
            cmd.Parameters.Add("@DonGia", SqlDbType.Float);
            cmd.Parameters.Add("@SoNgayThue", SqlDbType.Int);
            cmd.Parameters.Add("@ThanhTien", SqlDbType.Float);
            cmd.Parameters.Add("@TinhTrang",SqlDbType.Int);
            cmd.Parameters["@MaPhong"].Value = pt.MaPhong;
            cmd.Parameters["@NgayThuePhong"].Value = pt.NgayThuePhong;
            cmd.Parameters["@NgayKetThuc"].Value = pt.NgayKetThuc;
            cmd.Parameters["@DonGia"].Value = pt.DonGia;
            cmd.Parameters["@SoNgayThue"].Value = pt.SoNgayThue;
            cmd.Parameters["@ThanhTien"].Value = pt.ThanhTien;
            cmd.Parameters["@TinhTrang"].Value = 1;
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        public static DataTable LayMaxMaPhieuThue()
        {
            string sql = "select MAX(MaPhieuThue) from PHIEUTHUEPHONG";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable PhieuThueChuaThanhToan()
        {
            string sql = "Select pt.MaPhieuThue, p.TenPhong, pt.DonGia, pt.SoNgayThue, pt.NgayThuePhong, pt.NgayKetThuc,ctpt.MaKhachHang From PHIEUTHUEPHONG pt, PHONG p,CHITIETPHIEUTHUE ctpt Where p.MaPhong = pt.MaPhong and pt.TinhTrang =1 and pt.MaPhieuThue=ctpt.MaPhieuThue";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable ThanhTienTheoMa(int mapt)
        {
            string sql = "Select ThanhTien From PHIEUTHUEPHONG Where MaPhieuThue = " + mapt + "";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable PhongThueTheoMa(int mapt)
        {
            string sql = "Select MaPhong From PHIEUTHUEPHONG Where MaPhieuThue = " + mapt + "";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static void CapNhatPhieuThue(int mapt, int mahd)
        {
            string sql = string.Format("Update PHIEUTHUEPHONG Set MaHoaDon=" + mahd + ", TinhTrang=0 Where MaPhieuThue = " + mapt + "");
            DataProvider.ExecuteNonQuery(sql);
        }
        public static DataTable LayTongDoanhThuTheoThangBaoCao(int thangBaoCao, int namBaoCao)
        {
            string sql = "Select ThanhTien, NgayKetThuc From PHIEUTHUEPHONG  where Month(NgayKetThuc) = " + thangBaoCao + " and Year(NgayKetThuc) = " + namBaoCao;
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayPhieuThueTheoThangBaoCao(int thangBaoCao, int namBaoCao)
        {
            string sql = "Select lp.TenLoaiPhong, pt.ThanhTien From Phong p, PHIEUTHUEPHONG pt, LOAIPHONG lp where p.MaPhong = pt.MaPhong and p.MaLoaiPhong = lp.MaLoaiPhong and Month(pt.NgayKetThuc) = " + thangBaoCao + " and Year(pt.NgayKetThuc) = " + namBaoCao;
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayPhieuThueMatDoTheoThangBaoCao(int thangBaoCao, int namBaoCao)
        {
            string sql = "Select p.TenPhong, pt.SoNgayThue From PHONG p, PHIEUTHUEPHONG pt where p.MaPhong = pt.MaPhong and Month(pt.NgayKetThuc) = " + thangBaoCao + " and Year(pt.NgayKetThuc) = " + namBaoCao;
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayTongSoNgayThueTheoThangBaoCao(int thangBaoCao, int namBaoCao)
        {
            string sql = "Select SoNgayThue, NgayKetThuc From PHIEUTHUEPHONG  where Month(NgayKetThuc) = " + thangBaoCao + " and Year(NgayKetThuc) = " + namBaoCao;
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable LayPhieuThueTheoMa(int makh)
        {
            string sql = "Select p.TenPhong, pt.DonGia, pt.SoNgayThue, pt.NgayThuePhong, pt.NgayKetThuc, pt.ThanhTien From PHONG p, PHIEUTHUEPHONG pt, HOADON hd where p.MaPhong = pt.MaPhong and pt.MaHoaDon = hd.MaHoaDon and hd.MaKhachHang = " + makh;
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable Get()
        {
            string sql = "Select p.TenPhong, pt.DonGia, pt.SoNgayThue, pt.NgayThuePhong, pt.NgayKetThuc, pt.ThanhTien, pt.MaPhieuThue, pt.MaPhong From PHONG p, PHIEUTHUEPHONG pt where p.MaPhong = pt.MaPhong";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }
        public static DataTable PhieuThuePhongChuaThanhToan()
        {
            string sql = "Select pt.MaPhieuThue, p.TenPhong,pt.DonGia,pt.NgayThuePhong, pt.NgayKetThuc,pt.SoNgayThue,pt.ThanhTien From PHIEUTHUEPHONG pt, PHONG p Where p.MaPhong = pt.MaPhong and pt.TinhTrang = 0";
            DataTable dt = DataProvider.ExecuteQuery(sql);
            return dt;
        }

        public static void CapNhatPhieuThuePhong(int mapt, DateTime NgayKetThuc, int SoNgayThue, float ThanhTien)
        {
            string sql = string.Format("Update PHIEUTHUEPHONG  Set NgayKetThuc='" + NgayKetThuc + "',SoNgayThue='" + SoNgayThue + "',ThanhTien='" + ThanhTien + "' Where MaPhieuThue = '" + mapt + "'");
            DataProvider.ExecuteNonQuery(sql);
        }
        //************************************************//
        //Author:Nguyen Tan Phat
        //MSSV:1173025
        //Class:11HT
        #region
        public static int Insert(PhieuThuePhongDTO phieuthuephong)
        {
            var pr = new SqlParameter[3];
            pr[0] = new SqlParameter(@"MaPhieuThue", phieuthuephong.MaPhieuThue) { Direction = ParameterDirection.Output };
            pr[1] = new SqlParameter(@"MaPhong", phieuthuephong.MaPhong);
            pr[2] = new SqlParameter(@"NgayThuePhong", phieuthuephong.NgayThuePhong);
            SqlHelper.ExecuteNonQuery(ConnectionString.Text, CommandType.StoredProcedure, "PHIEUTHUEPHONG_Insert", pr);
            return Convert.ToInt32(pr[0].Value);
        }
        #endregion
        //************************************************//
    }
}
